<?php

namespace App\Admin\Http\Controllers;

use DB;
use Exception;
use Monolog\Logger;
use Monolog\Handler\StreamHandler;
use Monolog\Formatter\LineFormatter;

use App\Admin\Eloquent\User;
use App\Admin\Eloquent\Role;
use App\Admin\Eloquent\UserInfo;
use App\Admin\Eloquent\Project;
use App\Admin\Eloquent\TradeRecord;
use App\Admin\Eloquent\ProjectAttr;
use App\Admin\Eloquent\ProjectTypeTemplateAttribute;
use App\Admin\Eloquent\ProjectProfit;
use App\Admin\Eloquent\ProjectsUser;
use App\Admin\Eloquent\OrganizationalUser;

use Excel;

class MigrateController extends Controller
{
    /**
     * monolog 日志
     *
     * @var \Monolog\Logger
     */
    protected $logger;

    /**
     * 数据库连接
     *
     * @var \Illuminate\Database\Connection
     */
    protected $connection;

    public function __construct()
    {
        $this->_init();
    }

    public function getStart()
    {
        $this->logger->debug("------------------------Start Migrate Data-------------------------\n\n");
        echo "------------------------Start Migrate Data-------------------------<br><br>";
        // 经纪人
        // //$this->migrateBrokers();
        // // 投资人
        // $this->migrateUsers();
        // 融资人
        // //$this->migrateLoaner();
        // $this->migrateLoanDemand();
       
        // try{
        //     DB::transaction(function(){
        //         $user = User::where('username', 'liumingyang')->first();

        //         $info = $user->userInfo;
        //         $info->name = '刘名扬';
        //         $info->id_card = '50010119891204023X';
        //         $info->name_verified = 1;

        //         $info->save();

        //         // 新浪账户实名认证
        //         $args = [
        //             'real_name' => $info->name, 
        //             'cert_no' => $info->id_card, 
        //             'identity_id' => $user->identity_id
        //         ];

        //         $newResult = SetRealName::send($args);

        //         if($newResult['response_code']!='APPLY_SUCCESS'){
        //             throw new Exception("用户：{$user->username} 新浪账户实名认证失败! Error:" . $newResult['response_message']);
        //         }
        //     }); 
        // } catch (Exception $e) {
        //     echo $e->getMessage();
        // }
        $this->logger->debug("------------------------End Migrate Data-------------------------\n\n");
        echo "------------------------End Migrate Data-------------------------<br><br>";
        die;
    }

    public function getImport()
    {
        echo "------------------------update trade records-------------------------<br>";

        $projectUsers = ProjectsUser::where('born_trade_detail_id', '>', 0)->get();

        foreach ($projectUsers as $projectUser) {
            try{
                $trade = $this->connection->select("select * from trade_detail_item where trade_detail_id = {$projectUser->born_trade_detail_id}")[0];

                $projectUser->invest_time = $trade->create_date;

                $projectUser->save();
            } catch (Exception $e) {
                echo "Project User: ".$projectUsers->id . ",". $e->getMessage();
            }
        }
    }

    /**
     * 迁移经纪人数据
     *
     * @return void
     */
    protected function migrateBrokers()
    {
        $this->logger->debug("---------------------------开始迁移经纪人数据---------------------------\n");
        echo "---------------------------开始迁移经纪人数据---------------------------<br>";

        $sql = 'SELECT base.user_id, 
                        base.user_name, 
                        base.real_name,
                        base.mobile, 
                        base.mail,
                        base.log_password, 
                        relation.parent_id, 
                        relation.child_id, 
                        relation.org_code
                    FROM user_base_info AS base, user_relation AS relation
                    WHERE base.user_id = relation.child_id AND base.user_id IN (SELECT DISTINCT(user_id) FROM user_role r WHERE r.role_id = 11)';

        $users = $this->connection->select($sql);

        $totalCount = count($users);
        $errorCount = 0;

        foreach ($users as $u) {
            $this->logger->debug("------------>{$u->user_name}\t\t迁移开始");

            try {
                DB::transaction(function() use ($u) {
                    if (User::where('username', $u->user_name)->exists()) {
                        throw new Exception("{$u->user_name}\t\t已存在!");
                    }

                    $user = new User();
                    $user->username = $u->user_name;
                    $user->password = bcrypt($u->log_password);
                    $user->identity_id = $this->generateUniqueSinaIndetityId();
                    $user->scope = 'front';
                    $user->save();

                    $userInfo = new UserInfo();
                    $userInfo->user_id = $user->id;
                    $userInfo->name = $u->real_name;

                    switch ($u->org_code) {
                        case 'yx0001':
                            # 瑞信投资
                            $userInfo->org_code = '9DS12W';
                            $userInfo->dept_code = 'YNKNM3';
                            break;

                        case 'bw001':
                            # 编外
                            $userInfo->org_code = 'MCXR4V';
                            $userInfo->dept_code = 'PXS9C1';
                            break;
                        default:
                            # 默认营销机构
                            $userInfo->org_code = 'EZXWIC';
                            $userInfo->dept_code = 'SMDOWH';
                            break;
                    }

                    $userInfo->invite_code = $this->generateUniqueInviteCode();

                    $userInfo->broker_code = $userInfo->invite_code;

                    $userInfo->save();

                    // 添加经纪人角色
                    $user->roles()->attach([2, 4]);

                    $this->logger->debug("------------>{$u->user_name}\t\t迁移成功!\n");
                });
            } catch (Exception $e) {
                $errorCount += 1;
                $this->logger->debug("------------>迁移失败！".$e->getMessage(). "\n");
                echo "------------>迁移失败！".$e->getMessage(). "<br>";
            }
        }
        $this->logger->debug("---------------------------结束迁移经纪人数据。迁移数据".$totalCount."条，成功".($totalCount-$errorCount)."条，失败".$errorCount."条---------------------------\n\n");
        echo "---------------------------结束迁移经纪人数据。迁移数据".$totalCount."条，成功".($totalCount-$errorCount)."条，失败".$errorCount."条---------------------------<br><br>";
    }

    /**
     * 迁移投资人信息
     *
     * @return void
     */
    public function migrateUsers()
    {
        $this->logger->debug("---------------------------开始迁移投资人数据---------------------------\n");
        echo "---------------------------开始迁移投资人数据---------------------------<br>";
        $sql = 'SELECT base.user_id, 
                        base.user_name, 
                        base.real_name,
                        base.mobile, 
                        base.mail,
                        base.log_password, 
                        relation.parent_id, 
                        relation.child_id, 
                        relation.org_code,
                        parent.real_name as parent_real_name,
                        parent.user_name as parent_user_name
                    FROM user_base_info AS base, user_relation AS relation, (SELECT user_base_info.user_id, user_base_info.user_name, user_base_info.real_name FROM user_base_info) AS parent
                    WHERE base.user_id = relation.child_id AND base.user_id IN (SELECT DISTINCT(user_id) FROM user_role r WHERE r.role_id = 12) AND parent.user_id = relation.parent_id';

        $users = $this->connection->select($sql);

        $totalCount = count($users);
        $errorCount = 0;

        foreach ($users as $u) {
            $this->logger->debug("------------>{$u->user_name}\t\t迁移开始");

            try {
                DB::transaction(function() use ($u) {
                    if (User::where('username', $u->user_name)->exists()) {
                        throw new Exception("{$u->user_name}\t\t已存在!");
                    }

                    $broker = User::where('username', $u->parent_user_name)->first()->userInfo;

                    $user = new User();
                    $user->username = $u->user_name;
                    $user->password = bcrypt($u->log_password);
                    $user->identity_id = $this->generateUniqueSinaIndetityId();
                    $user->scope = 'front';
                    $user->save();

                    $userInfo = new UserInfo();
                    $userInfo->user_id = $user->id;
                    $userInfo->name = $u->real_name;
                    $userInfo->org_code = $broker->org_code;
                    $userInfo->dept_code = $broker->dept_code;
                    $userInfo->broker_code = $broker->invite_code;
                    $userInfo->invite_code = $this->generateUniqueInviteCode();

                    $userInfo->save();

                    // 添加投资人
                    $user->roles()->attach(2);

                    $this->logger->debug("------------>{$u->user_name}\t\t迁移成功!\n");
                });
            } catch (Exception $e) {
                $errorCount += 1;
                $this->logger->debug("------------>迁移失败！".$e->getMessage(). "\n");
                echo "------------>迁移失败！".$e->getMessage(). "<br>";
            }
        }
        $this->logger->debug("---------------------------结束迁投资人数据。迁移数据".$totalCount."条，成功".($totalCount-$errorCount)."条，失败".$errorCount."条---------------------------\n\n");
        echo "---------------------------结束迁投资人数据。迁移数据".$totalCount."条，成功".($totalCount-$errorCount)."条，失败".$errorCount."条---------------------------<br><br>";
    }

    protected function migrateLoaner()
    {
        $this->logger->debug("---------------------------开始迁移融资人数据---------------------------\n");
        echo "---------------------------开始迁移融资人数据---------------------------<br>";
        $sql = 'SELECT user_id,user_name,real_name,log_password,mobile,mail FROM user_base_info WHERE user_id IN (SELECT DISTINCT(user_id) FROM user_role r WHERE r.role_id = 13)';

        $users = $this->connection->select($sql);

        $totalCount = count($users);
        $errorCount = 0;

        foreach ($users as $u) {
            $this->logger->debug("------------>{$u->user_name}\t\t迁移开始");

            try {
                DB::transaction(function() use ($u) {
                    if (User::where('username', $u->user_name)->exists()) {
                        throw new Exception("{$u->user_name}\t\t已存在!");
                    }

                    $user = new User();
                    $user->username = $u->user_name;
                    $user->password = bcrypt($u->log_password);
                    $user->identity_id = $this->generateUniqueSinaIndetityId();
                    $user->scope = 'front';
                    $user->save();

                    $userInfo = new UserInfo();
                    $userInfo->user_id = $user->id;
                    $userInfo->name = $u->real_name;
                    $userInfo->org_code = 'EZXWIC';
                    $userInfo->dept_code = 'SMDOWH';
                    $userInfo->broker_code = '3FREE4';
                    $userInfo->invite_code = $this->generateUniqueInviteCode();

                    $userInfo->save();

                    // 添加投资人
                    $user->roles()->attach([2, 6]);

                    $this->logger->debug("------------>{$u->user_name}\t\t迁移成功!\n");
                });
            } catch (Exception $e) {
                $errorCount += 1;
                $this->logger->debug("------------>迁移失败！".$e->getMessage(). "\n");
                echo "------------>迁移失败！".$e->getMessage(). "<br>";
            }
        }
        $this->logger->debug("---------------------------结束迁融资人数据。迁移数据".$totalCount."条，成功".($totalCount-$errorCount)."条，失败".$errorCount."条---------------------------\n\n");
        echo "---------------------------结束迁融资人数据。迁移数据".$totalCount."条，成功".($totalCount-$errorCount)."条，失败".$errorCount."条---------------------------<br><br>";
    }

    /**
     * 初始化数据
     *
     * @return void
     */
    protected function _init()
    {
        $formatter = new LineFormatter(null, null, true, true);

        $handler = new StreamHandler(storage_path('/logs/migrate-data.log'), Logger::DEBUG);
        $handler->setFormatter($formatter);

        $logger = new Logger('local');
        $logger->pushHandler($handler);

        $this->logger = $logger;
        $this->connection = DB::connection('mysql2');
    }

    /**
     * 生成唯一邀请码
     *
     * @return string
     */
    protected function generateUniqueInviteCode()
    {
        do {
            $inviteCode = create_code(6);
        } while (UserInfo::where('invite_code', $inviteCode)->exists());

        return $inviteCode;
    }

    /**
     * 生成新浪支付 identity id
     *
     * @return string
     */
    protected function generateUniqueSinaIndetityId()
    {
        do {
            $identityId = createSinaIdentityId();
        } while (User::where('identity_id', $identityId)->exists());

        return $identityId;
    }

    protected function migrateLoanDemand()
    {
        $this->logger->debug("---------------------------开始迁移项目数据---------------------------\n");
        echo "---------------------------开始迁移项目数据---------------------------<br>";

        $sql = "
SELECT 
    # 项目 id
    demand.demand_id,
    # 交易代码
    demand.trade_code,
    # 融资人 id
    demand.loaner_id,
    # 融资人用户名
    demand.user_name,
    # 融资人姓名
    demand.loaner_name,
    # 项目名
    demand.loan_name,
    # 融资金额
    demand.loan_amount/100 as loan_amount,
    # 利率
    demand.interest_rate,
    # 借款期限
    demand.time_limit,
    # 借款期限的单位 D：天，W：周期，M：月，Y：年
    demand.time_limit_unit,
    # 最低投资金额
    demand.least_invest_amount/100 as least_invest_amount,
    # 标满条件计算方式：amount：金额，rate：百分比，date：日期
    demand.saturation_condition_method,
    # 标满条件
    demand.saturation_condition,
    # 借款用途
    demand.loan_purpose,
    # 有效期
    demand.deadline,
    # 标的物信息
    demand.loan_note,
    # 担保机构ID
    demand.guarantee_id,
    # 担保合同号
    demand.guarantee_licence_no,
    # 担保合同名称
    demand.guarantee_licence_name,
    # 上传担保函附件
    demand.guarantee_license_url,
    # 状态 'wite','pass','dismiss','offline','draft'
    demand.status,
    # 分润模板 id
    demand.division_template_id,
    # 项目创建时间
    demand.demand_date,
    # 发布时间
    demand.publish_date,
    # 还款方式（sit：到期归还本金及利息,month:按月还息，到期还本）
    demand.repay_division_way,
    # 可投资时间
    demand.invest_avl_date,
    # 业务类型：public所有人，其他类型
    demand.biz_type,
    # 抵押方式
    demand.insure_way,

    demand.loan_biz_type,
    # 产品类型: 001 企业经营贷, 002 债权质押贷, 003 车辆质押贷, 004 个人消费贷
    demand.area_nb_no,
    # 交易 id
    trade.trade_id,
    # 交易状态
    trade.trade_status,
    # 借款成立日期
    trade.trade_effective_date,
    # 借款到期日期
    trade.trade_expire_date,
    # 创建日期
    trade.trade_create_date,
    # 完成日期
    trade.trade_finish_date,
    # 投资完成时间
    trade.invest_finish_date,
    # 已投资数量
    trade.invest_count,
    # 已融资金额
    trade.loaned_amount/100 as loaned_amount
FROM loan_demand AS demand, trade
WHERE trade.trade_code = demand.trade_code and trade.demand_id = demand.demand_id";
        
        $loans =  $this->connection->select($sql);

        $totalCount = count($loans);
        $errorCount = 0;

        foreach ($loans as $loan) {
            $this->logger->debug("------------>{$loan->loan_name}\t\t迁移开始");
            try {
                DB::transaction(function() use ($loan){
                    # 融资人
                    $loaner = User::where('username', $loan->user_name)->first();

                    $loan_extens = collect($this->connection->select("select * from loan_demand_extend where loan_demand_id = {$loan->demand_id}"));

                    $rates = explode('+', $loan_extens->where('property_key', 'SALES_PROMOTION_PROMPT')->first()->property_value);

                    if (is_null($loaner)) {
                        throw new Exception("融资人[{$loan->user_name}]不存在！");
                    }

                    $project = new Project();
                    # 项目信息
                    $project->sn = $loan->trade_code; # 流水号
                    $project->projects_name = $loan->loan_name; # 项目名
                    $project->finance_money = $loan->loan_amount; # 借款金额
                    $project->rate = $loan->interest_rate; # 利率
                    
                    # 借款时间类型
                    switch ($loan->time_limit_unit) {
                        case 'W':
                            $project->finance_time_cate = 'mm';
                            break;
                        case 'M':
                            $project->finance_time_cate = 'm';
                            break;
                        case 'Y':
                            $project->finance_time_cate = 'y';
                            break;
                        default:
                            $project->finance_time_cate = 'd';
                            break;
                    }

                    switch ($loan->trade_status) {
                        case 1:
                            $project->status = 'rush';
                            break;
                        case 2:
                            $project->status = 'payment';
                            break;
                        case 3:
                            $project->status = 'finish';
                            break;
                        
                        default:
                            $project->status = 'rush';
                            break;
                    }

                    $project->finance_time_num = $loan->time_limit; # 借款时间
                    $project->finance_rule_money = $loan->least_invest_amount; # 起投金额
                    $project->full_code = 'pc'; # 满标方式
                    $project->full_num = $loan->saturation_condition*100; # 满标条件
                    $project->new_projects = $loan_extens->where('property_key', 'E8_NEWER_LOAN_DEMAND')->contains('property_value', 'Y'); # 是否新手标
                    # 显示利率
                    $project->auto_rate = count($rates) > 1 ? ($rates[0] . sprintf('<span>+%s</span>', $rates[1])) : $rates[0];

                    // $project->collateral = $loan->insure_way; # 抵押方式
                    switch ($loan->insure_way) {
                        case '0000000010':
                            $project->collateral = 'promise_type_1';
                            break;
                        case '0000000100':
                            $project->collateral = 'promise_type_2';
                            break;
                        case '0000001000':
                            $project->collateral = 'promise_type_3';
                            break;
                        
                        default:
                            $project->collateral = 'promise_type_2';
                            break;
                    }
                    
                    $project->repayment = $loan->repay_division_way; # 还款方式
                    $project->published_at = $loan->publish_date; # 发布时间
                    $project->financed_money = $loan->loan_amount; # 已借款金额
                    $project->created_at = $loan->demand_date; # 项目创建时间
                    $project->start_time = $loan->invest_avl_date; # 可投资时间
                    $project->end_time = $loan->deadline; # 可投资截止时间
                    $project->invest_finish_date = $loan->invest_finish_date; # 最后投资时间
                    $project->interest_at = $loan->trade_effective_date; # 项目成立时间
                    $project->trade_repayment_date = $loan->trade_expire_date; # 还款时间
                    $project->trade_finish_date = $loan->trade_finish_date; # 实际还款时间

                    # 借款人信息
                    $project->financier_id =  $loaner->id;
                    $project->financier_name = $loan->loaner_name;
                    
                    # 产品类型 id
                    $project->project_type_template_id = $loan->area_nb_no;

                    
                    # 担保机构信息
                    $project->collateral_id = 10;
                    $project->collateral_licence_no = $loan->guarantee_licence_no;
                    $project->collateral_license_url = $loan->guarantee_license_url;

                    # 分润模板 id
                    $division_template_trade = $this->connection->select("select * from division_template_trade where base_id = {$loan->division_template_id}")[0];

                    if ($division_template_trade->invest_template_id == 252 && $division_template_trade->repay_template_id == 251) {
                        $project->profits_cate = 3;
                    } elseif ($division_template_trade->invest_template_id == 253 && $division_template_trade->repay_template_id == 254) {
                        $project->profits_cate = 4;
                    } elseif ($division_template_trade->invest_template_id == 255 && $division_template_trade->repay_template_id == 256) {
                        $project->profits_cate = 5;
                    } elseif ($division_template_trade->invest_template_id == 257 && $division_template_trade->repay_template_id == 258) {
                        $project->profits_cate = 6;
                    } elseif ($division_template_trade->invest_template_id == 259 && $division_template_trade->repay_template_id == 260) {
                        $project->profits_cate = 7;
                    } elseif ($division_template_trade->invest_template_id == 261 && $division_template_trade->repay_template_id == 262) {
                        $project->profits_cate = 8;
                    } elseif ($division_template_trade->invest_template_id == 263 && $division_template_trade->repay_template_id == 264) {
                        $project->profits_cate = 9;
                    } elseif ($division_template_trade->invest_template_id == 265 && $division_template_trade->repay_template_id == 266) {
                        $project->profits_cate = 10;
                    } elseif ($division_template_trade->invest_template_id == 267 && $division_template_trade->repay_template_id == 268) {
                        $project->profits_cate = 11;
                    } elseif ($division_template_trade->invest_template_id == 269 && $division_template_trade->repay_template_id == 270) {
                        $project->profits_cate = 10;
                    } elseif ($division_template_trade->invest_template_id == 274 && $division_template_trade->repay_template_id == 275) {
                        $project->profits_cate = 13;
                    } elseif ($division_template_trade->invest_template_id == 276 && $division_template_trade->repay_template_id == 277) {
                        $project->profits_cate = 14;
                    } elseif ($division_template_trade->invest_template_id == 278 && $division_template_trade->repay_template_id == 279) {
                        $project->profits_cate = 15;
                    } elseif ($division_template_trade->invest_template_id == 280 && $division_template_trade->repay_template_id == 281) {
                        $project->profits_cate = 16;
                    } elseif ($division_template_trade->invest_template_id == 282 && $division_template_trade->repay_template_id == 283) {
                        $project->profits_cate = 17;
                    } elseif ($division_template_trade->invest_template_id == 284 && $division_template_trade->repay_template_id == 285) {
                        $project->profits_cate = 18;
                    } else {
                        throw new Exception('分润规则模板不存在');
                    }

                    $project->save();



                    $attr1 = new ProjectAttr();
                    $attr1->project_id =  $project->id;
                    $attr1->attr_name = '项目信息';
                    $attr1->attr_id = ProjectTypeTemplateAttribute::where('project_type_template_id', $project->project_type_template_id)->where('name', '项目信息')->first()->id;
                    $attr1->content = $loan->loan_note;
                    $attr1->sort = 10;
                    $attr1->save();

                    $attr2 = new ProjectAttr();
                    $attr2->project_id =  $project->id;
                    $attr2->attr_name = '承诺信息';
                    $attr2->attr_id = ProjectTypeTemplateAttribute::where('project_type_template_id', $project->project_type_template_id)->where('name', '承诺信息')->first()->id;
                    $attr2->sort = 2;
                    $attr2->content ='<p>
    <strong><span style="font-size:18px;font-family:宋体;color:black">承诺机构简介</span></strong>
</p>
<p style=";text-align: left;margin-top: auto;margin-bottom: auto">
    <span style=";font-family:宋体;color:#333333">重庆汉帮融资担保有限公司，成立于</span><span style=";font-family:">2002</span><span style=";font-family:宋体;color:#333333">年</span><span style=";font-family:">11</span><span style=";font-family:宋体;color:#333333">月</span>12<span style=";font-family:宋体;color:#333333">日，注册资本金</span><span style=";font-family:">1</span><span style=";font-family:宋体;color:#333333">亿元。</span>
</p>
<p style=";text-align: left;margin-top: auto;margin-bottom: auto">
    <span style=";font-family:宋体;color:#333333">公司具备完善的组织架构及专业团队，其经营范围如下：</span>
</p>
<p style=";text-align: left;margin-top: auto;margin-bottom: auto">
    <span style=";font-family:">-</span><span style=";font-family:宋体;color:#333333">单位及个人银行贷款或票据承兑、贴现提供担保；</span>
</p>
<p style=";text-align: left;margin-top: auto;margin-bottom: auto">
    <span style=";font-family:">-</span><span style=";font-family:宋体;color:#333333">企业的融资租赁、招投标等经济合同提供担保；</span>
</p>
<p style=";text-align: left;margin-top: auto;margin-bottom: auto">
    <span style=";font-family:">-</span><span style=";font-family:宋体;color:#333333">为企业诉讼及财产保全提供担保；</span>
</p>
<p>
    <span style=";font-family:">-</span><span style=";font-family:宋体;color:#333333">对外项目投资、股权投资、非权益性投资。</span>
</p>
<p>
    <span style=";font-family:宋体;color:#333333"><br/></span>
</p>
<p>
    <strong><span style="font-size:18px;font-family:宋体;color:black">机构资质</span></strong>
</p>
<p>
    <strong><span style="font-size:18px;font-family:宋体;color:black"></span></strong>
</p>
<p>
    <img src="/resources/web/images/hbdb.jpg" title="1461121147884033.jpg" alt="1461121147884033.jpg" width="600" height="388" border="0" vspace="0" style="width: 600px; height: 388px;"/>
</p>
<p>
    <strong><span style="font-size:18px;font-family:宋体;color:black">风控措施</span></strong>
</p>
<p>
    <span style=";font-family:宋体;color:#333333">由重庆汉帮融资担保有限公司提供连带责任反担保；</span>
</p>
<p>
    <span style=";font-family:宋体;color:#333333">其它风控措施详见相关证照附件。</span>
</p>
<p>
    <span style=";font-family:宋体;color:#333333"><br/></span>
</p>
<p>
    <strong><span style="font-size:18px;font-family:宋体;color:black">承诺机构意见</span></strong>
</p>
<p>
    <span style="font-size:14px;font-family:宋体;color:#333333">借款信息真实，还款来源有保障</span>
</p>
<p>
    <span style="font-size:14px;font-family:宋体;color:#333333"></span><span style="font-size:14px;font-family:宋体;color:#333333"></span>
</p>
<p>
    <span style=";font-family:宋体;color:#333333"><br/></span>
</p>
<p>
    <strong><span style="font-size:18px;font-family:宋体;color:black">承诺信息</span></strong>
</p>
<p>
    <span style="font-size:14px;font-family:宋体;color:#333333">$image_url$</span>
</p>
<p>
    <br/>
</p>';
                    $attr2->save();

                    $attr3 = new ProjectAttr();
                    $attr3->project_id =  $project->id;
                    $attr3->attr_name = '相关证照';
                    $attr3->attr_id = ProjectTypeTemplateAttribute::where('project_type_template_id', $project->project_type_template_id)->where('name', '相关证照')->first()->id;
                    $attr3->cate = 1;
                    $attr3->content = json_encode($this->connection->select("select request_path as full_url, '' as filename from common_attachment where biz_no = {$loan->demand_id} order by isort asc"));
                    $attr3->sort = 0;
                    $attr3->save();


                    $invest_records = $this->connection->select("SELECT * FROM trade_detail_item WHERE trade_id = {$loan->trade_id} AND transfer_phase = 'original' AND role_id = 12 ORDER BY create_date DESC");

                    foreach ($invest_records as $invest_record) {
                        $invester = User::where('username', $invest_record->user_name)->first();
                        $pu = new ProjectsUser();
                        $pu->born_trade_detail_id = $invest_record->trade_detail_id;
                        $pu->projects_id = $project->id;
                        $pu->user_id = $invester->id;
                        $pu->identity_id = $invester->identity_id;
                        $pu->invest_money = $invest_record->amount/100;
                        $pu->invest_time = $invest_record->create_date;
                        $pu->user_name = $invest_record->real_name;
                        $pu->org_code = $invester->userInfo->org_code;
                        $pu->dept_code = $invester->userInfo->dept_code;
                        $pu->broker_code = $invester->userInfo->broker_code;
                        $pu->save();
                    }

                    $repay_records = $this->connection->select("SELECT * FROM trade_detail_item WHERE trade_id = {$loan->trade_id} AND role_id != 13 AND transfer_phase in ('original', 'invest', 'repay') ORDER BY create_date DESC");

                    
                    foreach ($repay_records as $repay_record) {
               
                        //$this->logger->debug("------------>{$repay_record->user_name}！");

                        if ($repay_record->user_name == 'profit') {
                            $username = 'platform';
                        } elseif ($repay_record->user_name == 'platform') {
                            $username = 'bblfs';
                        }  elseif ($repay_record->user_name == 'yx0001') {
                            $username = 'rxtz';
                        } else {
                            $username = $repay_record->user_name;
                        }

                        $repayer = User::where('username', $username)->first();

                        $pp = new ProjectProfit();
                        $pp->born_trade_detail_id = $repay_record->trade_detail_id;
                        $pp->type = in_array($repay_record->transfer_phase, ['original', 'repay']) ? 1 : 2;
                        $pp->project_id = $project->id;
                        $pp->user_id = $repayer->id;
                        
                        $pp->project_user_id = 0;

                        switch ($repay_record->role_id) {
                            case 7:
                            case 9:
                                $pp->role_slug = 'OperatingPlatform';
                                $pp->user_name = $repayer->organizationalInfo->company_name;
                                break;
                            case 8:
                                $pp->role_slug = 'GuaranteeAgency';
                                $pp->user_name = $repayer->organizationalInfo->company_name;
                                break;
                            case 10:
                                $pp->role_slug = 'MarketingAgency';
                                $pp->user_name = $repayer->organizationalInfo->company_name;
                                break;
                            case 11:
                                $pp->role_slug = 'Agent';
                                $pp->user_name = $repayer->userInfo->name;
                                break;
                            case 12:
                                $pp->role_slug = 'User';
                                $pp->user_name = $repayer->userInfo->name;
                                break;
                            
                            default:
                                throw new Exception('未知角色');
                                break;
                        }

                        // if ($repayer->hasRole('OperatingPlatform')) {
                        //     $pp->role_slug = 'OperatingPlatform';
                        //     $pp->user_name = $repayer->organizationalInfo->company_name;
                        // } elseif ($repayer->hasRole('GuaranteeAgency')) {
                        //     $pp->role_slug = 'GuaranteeAgency';
                        //     $pp->user_name = $repayer->organizationalInfo->company_name;
                        // } elseif ($repayer->hasRole('MarketingAgency')) {
                        //     $pp->role_slug = 'MarketingAgency';
                        //     $pp->user_name = $repayer->organizationalInfo->company_name;
                        // } elseif ($repayer->hasRole('Agent')) {
                        //     $pp->role_slug = 'Agent';
                        //     $pp->user_name = $repayer->userInfo->name;
                        // } elseif ($repayer->hasRole('Loaner')) {
                        //     $pp->role_slug = 'Loaner';
                        //     $pp->user_name = $repayer->userInfo->name;
                        // } elseif ($repayer->hasRole('User')) {
                        //     $pp->role_slug = 'User';
                        //     $pp->user_name = $repayer->userInfo->name;
                        // } else {
                        //     throw new Exception('未知角色');
                        // }

                        switch ($repay_record->transfer_phase) {
                            case 'original':
                                $pp->money = $repay_record->amount/100;
                                $pp->profit = 0;
                                $pp->pay_time = $repay_record->actual_repay_date;
                                $pp->status = $repay_record->status == 'PROCESSED' ? 1 : 0;
                                break;
                            case 'repay':
                                $pp->money = 0;
                                $pp->profit = $repay_record->amount/100;
                                $pp->input_time = $repay_record->repay_date;
                                $pp->pay_time = $repay_record->actual_repay_date;
                                $pp->status = $repay_record->status == 'PROCESSED' ? 1 : 0;
                                break;
                            default:
                                $pp->money = $repay_record->amount/100;
                                $pp->profit = 0;
                                $pp->status = 1;
                                $pp->input_time = $repay_record->create_date;
                                $pp->pay_time = $repay_record->create_date;
                                break;
                        }

                        $pp->save();
                    }

                });
            } catch (Exception $e) {
                dd($e);
                $errorCount += 1;
                $this->logger->debug("------------>迁移失败！".$e->getMessage(). "\n");
                echo "------------>迁移失败！".$e->getMessage(). "<br>";
            }
        }
        $this->logger->debug("---------------------------结束迁项目数据。迁移数据".$totalCount."条，成功".($totalCount-$errorCount)."条，失败".$errorCount."条---------------------------\n\n");
         echo "---------------------------结束迁项目数据。迁移数据".$totalCount."条，成功".($totalCount-$errorCount)."条，失败".$errorCount."条---------------------------\n\n";
    }

    public function getUpdateAgentProfit()
    {
        $this->logger->debug("---------------------------开始更新经济人分润信息---------------------------\n");
        echo "---------------------------开始更新经济人分润信息---------------------------<br>";
        $trades = $this->connection->select('select * from trade_detail_item where role_id = 11');

        $totalCount = count($trades);
        $errorCount = 0;
        foreach ($trades as $trade) {
            try {
                DB::transaction(function() use ($trade){
                    $pp = ProjectProfit::where('born_trade_detail_id', $trade->trade_detail_id)->first();
                    $pu = ProjectsUser::where('born_trade_detail_id', $trade->note)->first();

                    $pp->project_user_id = $pu->id;
                    $pp->save();
                });

                 $this->logger->debug("------------>Trade Item: {$trade->trade_detail_id} 更新成功");
            } catch (Exception $e) {
                $errorCount += 1;
                $this->logger->debug("------------>Trade Item: {$trade->trade_detail_id}, error: ".$e->getMessage(). "\n");
                echo "Trade Item: {$trade->trade_detail_id}, error: ".$e->getMessage()."<br>";
            }
        }
        echo "---------------------------结束更新经济人分润信息。共".$totalCount."条，成功".($totalCount-$errorCount)."条，失败".$errorCount."条---------------------------<br><br>";
        $this->logger->debug("---------------------------结束更新经济人分润信息。共".$totalCount."条，成功".($totalCount-$errorCount)."条，失败".$errorCount."条---------------------------\n\n");
    }
}